import mysql.connector
import json
import os
from datetime import datetime
import traceback

# 默认MySQL配置
DEFAULT_CONFIG = {
    "host": "localhost",
    "port": 3306,
    "user": "root",
    "password": "",
    "database": "anjuke_spider",
    "table": "houses"
}

# 配置文件路径
CONFIG_FILE = os.path.join(os.path.dirname(os.path.dirname(os.path.abspath(__file__))), "config", "mysql_config.json")


def load_config():
    """
    加载MySQL配置，如果配置文件不存在则创建默认配置文件
    """
    # 确保配置目录存在
    config_dir = os.path.dirname(CONFIG_FILE)
    if not os.path.exists(config_dir):
        os.makedirs(config_dir)
    
    # 如果配置文件不存在，创建默认配置
    if not os.path.exists(CONFIG_FILE):
        with open(CONFIG_FILE, 'w', encoding='utf-8') as f:
            json.dump(DEFAULT_CONFIG, f, ensure_ascii=False, indent=4)
        print(f"[INFO] 已创建默认MySQL配置文件: {CONFIG_FILE}")
        return DEFAULT_CONFIG
    
    # 加载配置文件
    try:
        with open(CONFIG_FILE, 'r', encoding='utf-8') as f:
            config = json.load(f)
        print(f"[INFO] 已加载MySQL配置文件: {CONFIG_FILE}")
        return config
    except Exception as e:
        print(f"[ERROR] 加载MySQL配置文件失败: {e}")
        print(f"[INFO] 使用默认配置")
        return DEFAULT_CONFIG


class MySQLHandler:
    def __init__(self, city_pinyin=None, area_pinyin=None):
        """
        初始化MySQL处理器
        
        Args:
            city_pinyin: 城市拼音，例如：chongqing
            area_pinyin: 区域拼音，例如：banan, yubei
        """
        self.config = load_config()
        self.connection = None
        self.cursor = None
        
        # 如果提供了城市和区域，则动态设置表名
        if city_pinyin and area_pinyin:
            self.config['table'] = f"houses_{city_pinyin}_{area_pinyin}"
        else:
            # 使用默认表名
            self.config['table'] = self.config.get('table', 'houses')
    
    def connect(self):
        """
        连接到MySQL数据库
        """
        try:
            self.connection = mysql.connector.connect(
                host=self.config["host"],
                port=self.config["port"],
                user=self.config["user"],
                password=self.config["password"],
                database=self.config["database"]
            )
            self.cursor = self.connection.cursor()
            print(f"[INFO] 成功连接到MySQL数据库: {self.config['host']}:{self.config['port']}/{self.config['database']}")
            return True
        except mysql.connector.Error as err:
            if err.errno == mysql.connector.errorcode.ER_BAD_DB_ERROR:
                # 数据库不存在，尝试创建
                return self._create_database()
            else:
                print(f"[ERROR] 连接MySQL数据库失败: {err}")
                traceback.print_exc()
                return False
    
    def _create_database(self):
        """
        创建数据库和表
        """
        try:
            # 连接到MySQL服务器（不指定数据库）
            temp_connection = mysql.connector.connect(
                host=self.config["host"],
                port=self.config["port"],
                user=self.config["user"],
                password=self.config["password"]
            )
            temp_cursor = temp_connection.cursor()
            
            # 创建数据库
            temp_cursor.execute(f"CREATE DATABASE IF NOT EXISTS {self.config['database']} CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci")
            print(f"[INFO] 已创建数据库: {self.config['database']}")
            
            # 关闭临时连接
            temp_cursor.close()
            temp_connection.close()
            
            # 重新连接到新创建的数据库
            self.connection = mysql.connector.connect(
                host=self.config["host"],
                port=self.config["port"],
                user=self.config["user"],
                password=self.config["password"],
                database=self.config["database"]
            )
            self.cursor = self.connection.cursor()
            
            # 创建表
            self._create_table()
            
            print(f"[INFO] 成功连接到MySQL数据库: {self.config['host']}:{self.config['port']}/{self.config['database']}")
            return True
        except mysql.connector.Error as err:
            print(f"[ERROR] 创建数据库失败: {err}")
            traceback.print_exc()
            return False
    
    def _create_table(self):
        """
        创建房源数据表
        """
        try:
            # 创建表
            create_table_query = f"""
            CREATE TABLE IF NOT EXISTS {self.config['table']} (
                id INT AUTO_INCREMENT PRIMARY KEY,
                house_id VARCHAR(100) UNIQUE COMMENT '房源唯一标识',
                title VARCHAR(500) COMMENT '房源标题',
                total_price VARCHAR(50) COMMENT '总价',
                unit_price VARCHAR(50) COMMENT '单价',
                house_type VARCHAR(50) COMMENT '户型',
                area VARCHAR(50) COMMENT '面积',
                orientation VARCHAR(50) COMMENT '朝向',
                build_year VARCHAR(50) COMMENT '建造年份',
                floor VARCHAR(50) COMMENT '楼层',
                city VARCHAR(50) COMMENT '城市',
                area_detail VARCHAR(50) COMMENT '区域',
                district VARCHAR(50) COMMENT '区县',
                community VARCHAR(100) COMMENT '小区名称',
                address VARCHAR(255) COMMENT '地址',
                tags TEXT COMMENT '标签',
                crawl_time DATETIME COMMENT '爬取时间',
                INDEX idx_house_id (house_id),
                INDEX idx_community (community),
                INDEX idx_crawl_time (crawl_time)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
            """
            self.cursor.execute(create_table_query)
            self.connection.commit()
            print(f"[INFO] 已创建/确认表: {self.config['table']}")
            return True
        except mysql.connector.Error as err:
            print(f"[ERROR] 创建表失败: {err}")
            traceback.print_exc()
            return False

    def save_houses(self, houses):
        """
        将房源数据保存到MySQL数据库
        Args:
            houses: 房源数据列表，每个元素是一个字典
        Returns:
            bool: 是否成功保存
        """
        if not houses:
            print("[WARNING] 没有房源数据需要保存")
            return False
        # 确保连接到数据库
        if not self.connection or not self.cursor:
            if not self.connect():
                return False
        # 确保表存在
        self._create_table()
        # 当前时间
        now = datetime.now()
        try:
            # 准备插入语句
            insert_query = f"""
            INSERT INTO {self.config['table']} 
            (house_id, title, total_price, unit_price, house_type, area, orientation, build_year, floor, city, area_detail, district, community, address, tags, crawl_time)
            VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
            ON DUPLICATE KEY UPDATE
            house_id = VALUES(house_id),
            title = VALUES(title),
            total_price = VALUES(total_price),
            unit_price = VALUES(unit_price),
            house_type = VALUES(house_type),
            area = VALUES(area),
            orientation = VALUES(orientation),
            build_year = VALUES(build_year),
            floor = VALUES(floor),
            city = VALUES(city),
            area_detail = VALUES(area_detail),
            district = VALUES(district),
            community = VALUES(community),
            address = VALUES(address),
            tags = VALUES(tags),
            crawl_time = VALUES(crawl_time)
            """
            # 准备数据
            values = []
            for house in houses:
                values.append((
                    house.get("house_id", ""),
                    house.get("标题", ""),
                    house.get("总价", ""),
                    house.get("单价", ""),
                    house.get("户型", ""),
                    house.get("面积", ""),
                    house.get("朝向", ""),
                    house.get("建造年份", ""),
                    house.get("楼层", ""),
                    house.get("城市", ""),
                    house.get("区域", ""),
                    house.get("区县", ""),
                    house.get("小区", ""),
                    house.get("地址", ""),
                    house.get("标签", ""),
                    house.get("爬取时间", now)
                ))
            # 执行批量插入
            self.cursor.executemany(insert_query, values)
            self.connection.commit()
            print(f"[INFO] 成功将 {len(houses)} 条房源数据保存到MySQL数据库表 {self.config['table']}")
            return True
        except mysql.connector.Error as err:
            print(f"[ERROR] 保存数据到MySQL失败: {err}")
            traceback.print_exc()
            self.connection.rollback()
            return False
    
    def close(self):
        """
        关闭数据库连接
        """
        if self.cursor:
            self.cursor.close()
        if self.connection:
            self.connection.close()
        print("[INFO] MySQL连接已关闭")


# 使用示例
def save_to_mysql(houses, city_pinyin=None, area_pinyin=None):
    """
    将房源数据保存到MySQL数据库
    
    Args:
        houses: 房源数据列表
        city_pinyin: 城市拼音，例如：chongqing
        area_pinyin: 区域拼音，例如：banan, yubei
    
    Returns:
        bool: 是否成功保存
    """
    handler = MySQLHandler(city_pinyin, area_pinyin)
    try:
        result = handler.save_houses(houses)
        return result
    finally:
        handler.close()